Vilniaus miesto viesbuciai

Apzvelgti Vilniaus m. viesbuciu kiekvieno men. kainas 1 parai 2 asmenims.

Duomenu paemimas¶

In [10]:
pip install mysql-connector-python

Requirement already satisfied: mysql-connector-python in c:\users\tommo\anaconda3\lib\site-packages (8.0.27)
Requirement already satisfied: protobuf>=3.0.0 in c:\users\tommo\anaconda3\lib\site-packages (from mysql-connector-python) (3.19.1)
Note: you may need to restart the kernel to use updated packages.
In [1]:
import numpy as np
import mysql.connector
import pandas as pd
mydb = mysql.connector.connect(
    host="localhost",
    port="3317",
    user="root",
    password="...",
)
cursor = mydb.cursor()
cursor.execute('USE baigiamasis')
viesbuciai = pd.read_sql('select * from viesbuciai_1 where kaina not in (0)', con=mydb)
viesbuciai
Out[1]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk
0 1 Ivolita 2/1/2022 Viesbutis Geliu g. 5 47 25 IN 3
1 2 Mabre Residence 2/1/2022 Viesbutis Maironio g. 13 40 18 NOT IN 4
2 2 Mabre Residence 2/1/2022 Viesbutis Maironio g. 13 57 18 IN 4
3 9 Hilton Garden Inn Vilnius City Centre 1/1/2022 Viesbutis Gedimino av. 44 B 86 23 IN 4
4 9 Hilton Garden Inn Vilnius City Centre 1/1/2022 Viesbutis Gedimino av. 44 B 71 23 NOT IN 4
... ... ... ... ... ... ... ... ... ...
1546 8 Artis Centrum Hotels 2/16/2022 Viesbutis Totoriu g. 23 72 23 IN 4
1547 8 Artis Centrum Hotels 3/11/2022 Viesbutis Totoriu g. 23 72 23 IN 4
1548 8 Artis Centrum Hotels 8/15/2022 Viesbutis Totoriu g. 23 82 23 IN 4
1549 8 Artis Centrum Hotels 6/24/2022 Viesbutis Totoriu g. 23 82 23 IN 4
1550 8 Artis Centrum Hotels 7/6/2022 Viesbutis Totoriu g. 23 82 23 IN 4

1551 rows × 9 columns

Sprendimai¶

In [2]:
# 1. Pakeistas datos formatas

from datetime import date, time, datetime, timedelta
viesbuciai['Data'] = pd.to_datetime(viesbuciai['Data'], format = '%m/%d/%Y')
viesbuciai
Out[2]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk
0 1 Ivolita 2022-02-01 Viesbutis Geliu g. 5 47 25 IN 3
1 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 40 18 NOT IN 4
2 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 57 18 IN 4
3 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 86 23 IN 4
4 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 71 23 NOT IN 4
... ... ... ... ... ... ... ... ... ...
1546 8 Artis Centrum Hotels 2022-02-16 Viesbutis Totoriu g. 23 72 23 IN 4
1547 8 Artis Centrum Hotels 2022-03-11 Viesbutis Totoriu g. 23 72 23 IN 4
1548 8 Artis Centrum Hotels 2022-08-15 Viesbutis Totoriu g. 23 82 23 IN 4
1549 8 Artis Centrum Hotels 2022-06-24 Viesbutis Totoriu g. 23 82 23 IN 4
1550 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4

1551 rows × 9 columns

In [3]:
# 2.Kiek is viso viesbuciu.

viesbuciai.groupby('Pavadinimas').count()
Out[3]:
Id_nr Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk
Pavadinimas
15th Avenue 19 19 19 19 19 19 19 19
A.V.Goda 15 15 15 15 15 15 15 15
AirInn Vilnius Hotel 38 38 38 38 38 38 38 38
Amberton Cathedral Square Hotel 38 38 38 38 38 38 38 38
Amicus Hotel 34 34 34 34 34 34 34 34
... ... ... ... ... ... ... ... ...
Urbihop Hotel 19 19 19 19 19 19 19 19
Vilnius Apartments 19 19 19 19 19 19 19 19
Vilnius City Hotel 38 38 38 38 38 38 38 38
Vivulskio Apart-Hotel 7 7 7 7 7 7 7 7
ibis Vilnius 19 19 19 19 19 19 19 19

62 rows × 8 columns

In [4]:
# 3.Kiek zvaigzduciu turi viesbuciai

Zvaigzduciu_sk = viesbuciai.groupby(['Zvaigzduciu_sk'])
Zvaigzduciu_sk
Zvaigzduciu_sk['Pavadinimas'].value_counts()
Out[4]:
Zvaigzduciu_sk  Pavadinimas                       
1               Jeruzale Hotel                        36
2               Stay Express Hotel                    19
3               AirInn Vilnius Hotel                  38
                CALVARY Hotel & Restaurant Vilnius    38
                Corner Hotel                          38
                                                      ..
5               Grand Hotel Kempinski Vilnius         19
                Imperial Hotel & Restaurant           19
                NARUTIS hotel                         19
                Hotel PACAI                           17
                Relais & Châteaux Stikliai            16
Name: Pavadinimas, Length: 62, dtype: int64
In [5]:
# 4. Kokie viesbuciai siulo/nesiulo pusryciu.

viesbuciai[viesbuciai['Pusryciai'] == 'NOT IN'].groupby('Pavadinimas').count()
viesbuciai[viesbuciai['Pusryciai'] == 'IN'].groupby('Pavadinimas').count()
viesbuciai.groupby('Pusryciai')['Pavadinimas'].count()
Out[5]:
Pusryciai
IN        872
NOT IN    679
Name: Pavadinimas, dtype: int64
In [6]:
# 5. viesbuciai suskirstyti su pusryciais arba be

Pusryciai = viesbuciai.groupby(['Pusryciai'])
Pusryciai
Pusryciai['Pavadinimas'].value_counts()
Out[6]:
Pusryciai  Pavadinimas                       
IN         AirInn Vilnius Hotel                  19
           Amberton Cathedral Square Hotel       19
           Artagonist Art Hotel                  19
           Artis Centrum Hotels                  19
           CALVARY Hotel & Restaurant Vilnius    19
                                                 ..
NOT IN     A.V.Goda                               5
           City Hotels R?dninkai                  5
           Green Vilnius Hotel                    5
           Hotel Apia                             5
           Artis Centrum Hotels                   1
Name: Pavadinimas, Length: 98, dtype: int64
In [25]:
# 6. Vieno kvadrato nuomos kaina

viesbuciai['Kvadrato_kaina'] = viesbuciai['Kaina'] / viesbuciai['Kambario m²']
viesbuciai
Out[25]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina Ketvirtis
0 1 Ivolita 2022-02-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1
1 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 40 18 NOT IN 4 2.222222 2022Q1
2 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 57 18 IN 4 3.166667 2022Q1
3 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 86 23 IN 4 3.739130 2022Q1
4 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 71 23 NOT IN 4 3.086957 2022Q1
... ... ... ... ... ... ... ... ... ... ... ...
1546 8 Artis Centrum Hotels 2022-02-16 Viesbutis Totoriu g. 23 72 23 IN 4 3.130435 2022Q1
1547 8 Artis Centrum Hotels 2022-03-11 Viesbutis Totoriu g. 23 72 23 IN 4 3.130435 2022Q1
1548 8 Artis Centrum Hotels 2022-08-15 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3
1549 8 Artis Centrum Hotels 2022-06-24 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q2
1550 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3

1551 rows × 11 columns

In [45]:
# Didziausias siulomas kambarys

viesbuciai.iloc[:, [6]].max()
Out[45]:
Kambario m²    53
dtype: int64
In [32]:
# Maziausias siulomas kambarys

viesbuciai.iloc[:, [6]].min()
Out[32]:
Kambario m²    6
dtype: int64
In [8]:
# 7. Vidutine kaina , kvadratu skaicius

viesbuciai.iloc[:, [1, 5, 6, 9]].mean()
Out[8]:
Kaina             82.601547
Kambario m²       18.551257
Kvadrato_kaina     4.445634
dtype: float64
In [9]:
# 8. Auksciausia Kaina

viesbuciai.groupby('Data')['Kaina'].max().max()
viesbuciai[viesbuciai['Kaina'] == 452]
Out[9]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina
1033 49 Imperial Hotel & Restaurant 2022-12-01 Viesbutis Subaciaus g. 2 452 53 IN 5 8.528302
1034 49 Imperial Hotel & Restaurant 2022-12-25 Viesbutis Subaciaus g. 2 452 53 IN 5 8.528302
In [10]:
# 9. Zemiausia kaina

viesbuciai.groupby('Data')['Kaina'].min().min()
viesbuciai[viesbuciai['Kaina'] == 34]
Out[10]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina
737 37 Vilnius Apartments 2022-09-01 Viesbutis Sv. Stepono g. 8 34 16 NOT IN 3 2.125
739 37 Vilnius Apartments 2022-11-01 Viesbutis Sv. Stepono g. 8 34 16 NOT IN 3 2.125
740 37 Vilnius Apartments 2022-12-01 Viesbutis Sv. Stepono g. 8 34 16 NOT IN 3 2.125
741 37 Vilnius Apartments 2022-12-25 Viesbutis Sv. Stepono g. 8 34 16 NOT IN 3 2.125
In [11]:
viesbuciai.groupby('Data')['Kaina'].mean().plot(kind='barh', figsize=(7,7))
Out[11]:
<AxesSubplot:ylabel='Data'>
In [12]:
# 10. Sukurtas ketvircio stulpelis.

viesbuciai['Ketvirtis'] = viesbuciai['Data'].dt.to_period('q')
viesbuciai
Out[12]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina Ketvirtis
0 1 Ivolita 2022-02-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1
1 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 40 18 NOT IN 4 2.222222 2022Q1
2 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 57 18 IN 4 3.166667 2022Q1
3 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 86 23 IN 4 3.739130 2022Q1
4 9 Hilton Garden Inn Vilnius City Centre 2022-01-01 Viesbutis Gedimino av. 44 B 71 23 NOT IN 4 3.086957 2022Q1
... ... ... ... ... ... ... ... ... ... ... ...
1546 8 Artis Centrum Hotels 2022-02-16 Viesbutis Totoriu g. 23 72 23 IN 4 3.130435 2022Q1
1547 8 Artis Centrum Hotels 2022-03-11 Viesbutis Totoriu g. 23 72 23 IN 4 3.130435 2022Q1
1548 8 Artis Centrum Hotels 2022-08-15 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3
1549 8 Artis Centrum Hotels 2022-06-24 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q2
1550 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3

1551 rows × 11 columns

In [43]:
# 10.1. sukurta bendra lentele su ketvircio kainomis max/vid/min

Ketvirciai_mean = viesbuciai.groupby(['Ketvirtis','Pavadinimas', 'Id_nr'])['Kaina'].mean().reset_index()
Ketvirciai_max = viesbuciai.groupby(['Ketvirtis','Pavadinimas', 'Id_nr'])['Kaina'].max().reset_index()
Ketvirciai_min = viesbuciai.groupby(['Ketvirtis','Pavadinimas', 'Id_nr'])['Kaina'].min().reset_index()

Ketvirciai_1 = Ketvirciai_mean.join(Ketvirciai_max, on='Id_nr', how='inner', lsuffix='_mean', rsuffix='_max')
Ketvirciai = Ketvirciai_1.join(Ketvirciai_min, on='Id_nr', lsuffix='_min') 
Ketvirciai = Ketvirciai.drop(['Ketvirtis_max', 'Pavadinimas_max', 'Id_nr_max', 'Ketvirtis', 'Pavadinimas', 'Id_nr'], axis = 1)

Ketvirciai.rename(columns={'Ketvirtis_mean':'Ketvirtis', 'Pavadinimas_mean':'Pavadinimas', 'Id_nr_mean':'Id_nr', 'Kaina':'Kaina_min' }, inplace=True)

Ketvirciai
Out[43]:
Id_nr_min Ketvirtis Pavadinimas Id_nr Kaina_mean Kaina_max Kaina_min
0 34 2022Q1 15th Avenue 34 60.0 55 47
62 34 2022Q2 15th Avenue 34 66.8 55 47
118 34 2022Q3 15th Avenue 34 78.0 55 47
168 34 2022Q4 15th Avenue 34 78.0 55 47
1 58 2022Q1 A.V.Goda 58 46.0 50 35
... ... ... ... ... ... ... ...
116 53 2022Q2 Vivulskio Apart-Hotel 53 35.0 167 83
61 19 2022Q1 ibis Vilnius 19 50.6 272 232
117 19 2022Q2 ibis Vilnius 19 88.8 272 232
167 19 2022Q3 ibis Vilnius 19 113.6 272 232
213 19 2022Q4 ibis Vilnius 19 104.0 272 232

214 rows × 7 columns

In [36]:
# 10.2 apibendrintos kainos pagal ketvirti

viesbuciai.groupby('Ketvirtis')['Kaina'].min()
viesbuciai.groupby('Ketvirtis')['Kaina'].max()
viesbuciai.groupby('Ketvirtis')['Kaina'].mean()
Out[36]:
Ketvirtis
2022Q1    272
2022Q2    292
2022Q3    292
2022Q4    452
Freq: Q-DEC, Name: Kaina, dtype: int64

minimali kaina - 34 maksimali kaina - 452

In [34]:
viesbuciai.groupby('Ketvirtis')['Kaina'].mean().plot(kind='barh', figsize=(10,5))
Out[34]:
<AxesSubplot:ylabel='Ketvirtis'>
In [37]:
# 11. sukurtos lenteles su kainomis max/vid/min pagal zvaigzduciu sk su kiekviena data

Zvaigzdes_1 = viesbuciai.groupby(['Zvaigzduciu_sk', 'Data'])['Kaina'].max().reset_index()
Zvaigzdes_1
Zvaigzdes_2 = viesbuciai.groupby(['Zvaigzduciu_sk', 'Data'])['Kaina'].min().reset_index()
Zvaigzdes_2
Zvaigzdes_3 = viesbuciai.groupby(['Zvaigzduciu_sk', 'Data'])['Kaina'].mean().reset_index()
Zvaigzdes_1
Out[37]:
Zvaigzduciu_sk Data Kaina
0 1 2022-01-01 40.000000
1 1 2022-02-01 40.000000
2 1 2022-02-16 40.000000
3 1 2022-03-01 40.000000
4 1 2022-03-11 40.000000
... ... ... ...
89 5 2022-09-01 186.714286
90 5 2022-10-01 187.428571
91 5 2022-11-01 190.000000
92 5 2022-12-01 205.800000
93 5 2022-12-25 205.800000

94 rows × 3 columns

In [40]:
# 11.1. apibendrintos kainos pagal zvaigzduciu skaiciu

viesbuciai.groupby('Zvaigzduciu_sk')['Kaina'].min()

viesbuciai.groupby('Zvaigzduciu_sk')['Kaina'].max()

viesbuciai.groupby('Zvaigzduciu_sk')['Kaina'].mean()
Out[40]:
Zvaigzduciu_sk
1     40.000000
2     43.842105
3     58.439222
4     88.846871
5    180.164062
Name: Kaina, dtype: float64

Minimali: 1 - 36, 2 - 42, 3 - 34, 4 - 40, 5 - 53. Maksimali 1 - 44, 2 - 52, 3 - 120, 4 - 167, 5 - 452

In [24]:
#  13. Viesbucio vidutine kaina pagal zvaigzdutes

viesbuciai3 = viesbuciai_1.groupby(['Zvaigzduciu_sk', 'Ketvirtis_Q'])['Kaina'].mean().reset_index()
viesbuciai3[viesbuciai3['Zvaigzduciu_sk'] == 5]
#  1 zv. - kaina nesikeicia
#  2 zv. - kaina didziausia 1 ketv.
#  3 zv. - kaina didziausia 3 ketv.
#  4 zv. - kaina didziausia 3 ketv.
#  5 zv. - kaina didziausia 4 ketv.
Out[24]:
Zvaigzduciu_sk Ketvirtis_Q Kaina
16 5 2022Q1 166.542857
17 5 2022Q2 183.000000
18 5 2022Q3 180.485714
19 5 2022Q4 196.086957
In [21]:
# 14. sujungta lentele viesbuciai su ketvirciu lentele.

viesbuciai_1 = pd.merge(viesbuciai, Ketvirciai, on=['Id_nr'], suffixes=('_v', '_k')) 
viesbuciai_1
viesbuciai_1 = viesbuciai_1.drop(['Ketvirtis_k', 'Pavadinimas_k'], axis=1)
viesbuciai_1.rename(columns={'Pavadinimas_v':'Pavadinimas','Ketvirtis_v':'Ketvirtis_Q','Kaina_mean':'Kaina_mean_Q','Kaina_max':'Kaina_max_Q', 'Kaina_min':'Kaina_min_Q'}, inplace=True)
viesbuciai_1
Out[21]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina Ketvirtis_Q Kaina_mean_Q Kaina_max_Q Kaina_min_Q
0 1 Ivolita 2022-02-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47
1 1 Ivolita 2022-03-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47
2 1 Ivolita 2022-01-01 Viesbutis Geliu g. 5 55 25 IN 3 2.200000 2022Q1 49.000000 55 47
3 1 Ivolita 2022-02-16 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47
4 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 40 18 NOT IN 4 2.222222 2022Q1 51.200000 67 40
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5841 8 Artis Centrum Hotels 2022-06-24 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q2 72.000000 72 72
5842 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 78.833333 97 72
5843 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 78.000000 82 72
5844 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 82.000000 82 82
5845 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 72.000000 72 72

5846 rows × 14 columns

In [22]:
# 15. Jeigu paros kaina didesne nei vidutine ketvircio paros kaina grazina True

viesbuciai_1['Ar_pasiekia_vidurki'] = np.where(viesbuciai_1['Kaina'] >=viesbuciai_1['Kaina_mean_Q'], 'True', 'False')
viesbuciai_1
Out[22]:
Id_nr Pavadinimas Data Kategorija Adresas Kaina Kambario m² Pusryciai Zvaigzduciu_sk Kvadrato_kaina Ketvirtis_Q Kaina_mean_Q Kaina_max_Q Kaina_min_Q Ar_pasiekia_vidurki
0 1 Ivolita 2022-02-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47 False
1 1 Ivolita 2022-03-01 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47 False
2 1 Ivolita 2022-01-01 Viesbutis Geliu g. 5 55 25 IN 3 2.200000 2022Q1 49.000000 55 47 True
3 1 Ivolita 2022-02-16 Viesbutis Geliu g. 5 47 25 IN 3 1.880000 2022Q1 49.000000 55 47 False
4 2 Mabre Residence 2022-02-01 Viesbutis Maironio g. 13 40 18 NOT IN 4 2.222222 2022Q1 51.200000 67 40 False
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
5841 8 Artis Centrum Hotels 2022-06-24 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q2 72.000000 72 72 True
5842 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 78.833333 97 72 True
5843 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 78.000000 82 72 True
5844 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 82.000000 82 82 True
5845 8 Artis Centrum Hotels 2022-07-06 Viesbutis Totoriu g. 23 82 23 IN 4 3.565217 2022Q3 72.000000 72 72 True

5846 rows × 15 columns

In [23]:
import matplotlib.pyplot as plt
viesbuciai_1.plot.bar(x='Ketvirtis_Q', y='Kaina_mean_Q', figsize=(10,2))
plt.show()
In [ ]: